package com.itheima.csdn_hongchen.method;

import java.sql.*;

public class DbConn {
    public static Connection GetConn() {
        String url = "jdbc:mysql://localhost:3306/my_new_database?useSSL=false&serverTimezone=UTC";
        String user = "root"; // 替换为你的MySQL用户名
        String password = "CST200212"; // 替换为你的MySQL密码
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    // 增加
    public static void InsertData(String itemName, String value) throws SQLException {
        String sql = "INSERT INTO first_table (item_name, Value) VALUES (?, ?)";
        Connection conn = GetConn();
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, itemName);
        preparedStatement.setString(2, value);
        preparedStatement.executeUpdate();

    }

    //删除操作
    public static void deleteData(int id) throws SQLException {
        String sql = "DELETE FROM first_table WHERE Id = ?";
        Connection conn = DbConn.GetConn();
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        preparedStatement.executeUpdate();
    }

    //查询操作
    public static void selectData() throws SQLException {
        String sql = "SELECT * FROM first_table ";
        Connection conn = GetConn();
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        // preparedStatement.setInt(1, id);
        ResultSet rs = preparedStatement.executeQuery();

        while (rs.next()) {
            String id = rs.getString("id");
            String itemName = rs.getString("item_name");
            String value = rs.getString("Value");
            System.out.println("ID: "  + ", Item Name: " + itemName + ", Value: " + value);
        }
    }
//更新数据
    public static void updateData(int id, String price) throws SQLException {
        String sql = "UPDATE first_table SET Value = ? WHERE Id = ?";
        Connection conn = GetConn();
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, price);
        preparedStatement.setInt(2, id);
        preparedStatement.executeUpdate();
    }

}
